package com.btm.distrb.detection;

import com.btm.distrb.model.Column;
import com.btm.distrb.model.Table;
import javafx.scene.control.Tab;

import java.sql.*;
import java.util.*;

/**
 * 对比数据表
 */
public class TableDetection {

    public static final String SQL_ALL_COLUMN_PREFIX = "select * from ";
    public static final String SHOW_COLUMN_ANNOTATION = "show full columns from ";

    private Connection connection;

    public TableDetection(Connection connection) {
        this.connection = connection;
    }

    /**
     * 获得数据库表中全部表
     *
     * @param prefix 表名前缀
     * @return
     * @throws SQLException
     */
    public List<String> getAllTableNames(String catalog, String... prefix) {
        ResultSet resultSet = null;
        List<String> tableNames = new ArrayList<String>();
        try {
            if (null == connection) {
                throw new SQLException("not sql connection.");
            }
            DatabaseMetaData dmd = connection.getMetaData();
            resultSet = dmd.getTables(catalog, null, null, new String[]{"TABLE"});
            while (resultSet.next()) {
                if (null != prefix && 0 < prefix.length) {
                    for (String str : prefix) {
                        if (resultSet.getString(3).startsWith(str)) {
                            tableNames.add(resultSet.getString(3));
                        }
                    }
                } else {
                    tableNames.add(resultSet.getString(3));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (null != resultSet) {
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return tableNames;
    }

    public Map<String, Column> getColumnNameMapByTableName(Table table) {
        Map<String, Column> maps = new HashMap<String, Column>();
        List<Column> list = getColumnNameListByTableName(table);
        for (Column column : list) {
            maps.put(column.getColumnName(), column);
        }
        list.clear();
        return maps;
    }

    /**
     * 获得数据库表的全部字段
     *
     * @param table
     * @return
     */
    public List<Column> getColumnNameListByTableName(Table table) {
        List<Column> columns = new ArrayList<Column>();
        PreparedStatement preparedStatement = null;
        ResultSetMetaData resultSetMetaData = null;
        try {
            if (null == connection) {
                throw new SQLException("not sql connection.");
            }
            preparedStatement = connection.prepareStatement(SQL_ALL_COLUMN_PREFIX + table.getTableName());
            resultSetMetaData = preparedStatement.getMetaData();
            int size = resultSetMetaData.getColumnCount();
            for (int i = 1; i <= size; i++) {
                Column column = new Column();
                column.setColumnName(resultSetMetaData.getColumnName(i));
                column.setColumnType(resultSetMetaData.getColumnType(i));
                column.setTable(table);
                columns.add(column);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (null != preparedStatement) {
                try {
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return columns;
    }

    /**
     * 获得数据库实例中全部的表、字段数据
     *
     * @return
     */
    public Map<String, Table> getAllTableAndColumn(String catalog, Map<String, Table> standardMap, String... prefix) {
        Map<String, Table> tableMap = new HashMap<>();
        List<String> tableNames = getAllTableNames(catalog, prefix);
        for (String tableName :
                tableNames) {
            Table table = new Table();
            table.setTableName(tableName);
            Map<String, Column> columns = getColumnNameMapByTableName(table);
            if (null == standardMap) {
                table.setCatalog(catalog);
                table.setTableName(tableName);
                table.setColumn(columns);
                tableMap.put(tableName, table);
            } else {
                Table stdTable = standardMap.get(tableName);
                if (null == stdTable) {
                    System.out.println("Standard DataBase do not have table : " + tableName);
                    continue;
                }
                Map<String, Column> stdColumn = stdTable.getColumn();
                for (Map.Entry<String, Column> entry :
                        stdColumn.entrySet()) {
                    Column column = columns.get(entry.getKey());
                    if (null == column) {
                        System.out.println("[" + catalog + ":" + tableName + "] do not have column : " + entry.getKey());
                    } else if (!column.equals(entry.getValue())) {
                        System.out.println("[" + catalog + ":" + tableName + "] column " + column.getColumnName() + " do not like [" + entry.getValue().getTable().getCatalog() + ":" + entry.getValue().getTable().getTableName() + "] column : " + entry.getKey());
                    }
                    columns.remove(entry.getKey());
                }
                //检查目标库是否还有字段
                if (0 < columns.size()) {
                    System.out.print("[" + catalog + ":" + tableName + "] surplus down list columns : ");
                    for (Map.Entry<String, Column> entry : columns.entrySet()) {
                        System.out.print(entry.getKey() + " ");
                    }
                    System.out.println();
                }
            }
        }
        return tableMap;
    }
}
